<?php
class path_model extends CI_Model{
    public function __construct() {
        parent::__construct();
    }
    
    public function browseId($id) {
        $this->db->select("*");
        $this->db->where("id","$id");
        $query = $this->db->get("learning_path");
        return $query->result_array();
    }
    
    public function getPathNewestVersion($path_version_id) {
        $this->db->select("*");
        $this->db->where("learning_path_id", $path_version_id);
        $this->db->order_by("version desc");
        $this->db->limit(1,0);
        $query = $this->db->get("path_version");
        return $query->result_array();
    }
    
    public function getInformation($path_version_id) {
        $query = $this->db->query("select v.version, v.modified_date, m.description
            from path_version v, path_meta m
            where m.id=v.path_meta_id AND v.id=" . $path_version_id);
        return $query->result_array();
    }
    
    public function getPathSection($path_version_id) {
        $this->db->select("*");
        $this->db->where("path_version_id", $path_version_id);
        $query = $this->db->get("path_section");
        return $query->result_array();
    }
    
    public function getSection($path_version_id) {
        $query = $this->db->query("select s.id, s.title, s.description
            from section_meta s, path_section p
            where s.id=p.section_meta_id AND p.path_version_id=" . $path_version_id);
        return $query->result_array();
    }
    
    public function getAction($section_id) {
        $sql = "select a.id, a.type, a.title, a.link
            from section_meta s, action a
            where s.id=a.section_meta_id AND s.id=" . $section_id;
        $query = $this->db->query($sql);
        //print($sql);
        return $query->result_array();
    }
    
    public function getContributor($path_id) {
        $query = $this->db->query("select m.id, m.fullname
            from member m, path_version p
            where m.id=p.contributor_id AND p.learning_path_id=" . $path_id
                . " order by p.version desc");
        return $query->result_array();
    }
    
    public function getTag($path_id) {
        $query = $this->db->query("select t.id, t.title, t.path_number
            from tag t, path_has_tag p
            where t.id=p.tag_id AND p.learning_path_id=" . $path_id
                . " order by t.title asc");
        return $query->result_array();
    }
    
    public function getTagList() {
        $query = $this->db->query("SELECT id, title FROM tag");
        return $query->result_array();
    }
    
    public function commentNumber($path_id) {
        $query = $this->db->query("select count(id) number from path_discussion where learning_path_id=" . $path_id);
        $r = $query->result_array();
        return $r[0]["number"];
    }
    
    public function ratingNumber($path_id) {
        $query = $this->db->query("select count(id) number from path_rating where learning_path_id=" . $path_id);
        $r = $query->result_array();
        return $r[0]["number"];
    }
    
    /**
     *All below methods are for creating path
     */
    
    public function createLearningPath($data) {
        $id=array();
        //Insert to learning_path table and get the inserted id.
        $title = $this->db->escape_str($data["title"]);
        $sql_insertPath = "INSERT INTO learning_path(title, field_id, permission)
            VALUES (\"" . $title . "\", " . $data["field_id"] . ", " . $data["permission"] . "" . ")";
        $this->db->query($sql_insertPath);
        $insertedLearningPathID = $this->db->insert_id();
        $id['path'] = $insertedLearningPathID;
        
        //Insert to path_meta table and get the inserted id.
        $description = $this->db->escape_str($data["description"]);
        $sql_insertPathMeta = "INSERT INTO path_meta(description) VALUE(\"".$description."\")";
        $this->db->query($sql_insertPathMeta);
        $insertedPathMetaID = $this->db->insert_id();
        
        //Insert to path_version table
        $sql_insertPathVersion = "INSERT INTO path_version(version, modified_date, path_meta_id,
            learning_path_id, contributor_id)
            VALUE (\"1\", now(),\"".$insertedPathMetaID."\",\"".$insertedLearningPathID."\",\"".$data["member_id"]."\")";
        $this->db->query($sql_insertPathVersion);
        $insertedPathVersionID = $this->db->insert_id();
        $id['path_version'] = $insertedPathVersionID;
        
        /////////////////////
        
        //Insert to tag table
        if (is_array($data["tags"]) && count($data["tags"]) > 0) {
            $data["tags"]=  array_unique($data["tags"]);//Remove duplicate elements;
            foreach ($data["tags"] as $value) {
                $newTag= $this->db->escape_str($value);
                $sql_checkExistTag = "SELECT id FROM tag WHERE title=\"" . $newTag . "\"";
                $query = $this->db->query($sql_checkExistTag);
                $row = $query->result_array();
                $isExistTag = (count($row) > 0 && $row[0]["id"] != null);
                
                if ($isExistTag) {
                    //Old tag
                    $this->db->query("INSERT INTO path_has_tag(learning_path_id, tag_id) 
                        VALUE (\"".$insertedLearningPathID."\",\"".$row[0]["id"]."\")");
                    $sql = "update tag set
                                path_number = path_number + 1 where id=" . $row[0]["id"];
                    $this->db->query($sql);
                } else {
                    //Insert new tag
                    $sql_insertNewTag = "INSERT INTO tag(title, path_number) VALUE(\"" . $newTag . "\", 1)";
                    $this->db->query($sql_insertNewTag);
                    $insertedTagID = $this->db->insert_id();
                    $this->db->query("INSERT INTO path_has_tag(learning_path_id, tag_id)
                    VALUE (\"" . $insertedLearningPathID . "\",\"" . $insertedTagID . "\")");
                }
            }
        }
        return $id;
    }
    
    public function addLevelActionToLearningPath($insertedPathVersionID, $data) {
        foreach ($data["level"] as $value) {
            //Insert to section_meta table
            $title = $this->db->escape_str($value["title"]);
            $description = $this->db->escape_str(base64_decode($value["description"]));
            $sql_insertSectionMeta = "INSERT INTO section_meta(title, description)
                VALUES (\"". $title ."\", \"". $description ."\")";
            $this->db->query($sql_insertSectionMeta);
            $insertedSectionMetaID = $this->db->insert_id();
            
            //Insert to path_section table
            $sql_insertPathSection = "INSERT INTO path_section(path_version_id, section_meta_id)
                VALUES ('".$insertedPathVersionID."', '".$insertedSectionMetaID."')";
            $this->db->query($sql_insertPathSection);
            
            foreach ($value["action"] as $action) {
                $title = $this->db->escape_str($action["title"]);
                $link = $this->db->escape_str($action["link"]);
                $sql_insertAction = "INSERT INTO action(type, title, link, section_meta_id)
                    VALUES (".$action["type"].", '".$title."', '".$link."', ".$insertedSectionMetaID.")";
                $this->db->query($sql_insertAction);
            }
        }
    }
    
    public function getArticleList() {
        $query = $this->db->query("SELECT a.id, a.title, v.contributor_id
            FROM article a, article_version v
            WHERE a.id=v.article_id
                AND v.version=(SELECT max(version) FROM article_version WHERE article_id=a.id)");
        return $query->result_array();
    }
}

/* End of file path_model.php */
/* Location: ./application/models/path_model.php */